package com.dp.service.menucontrol;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.SnAreaVo;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.DataMap;
import com.dp.vo.NewsGroup;
import com.dp.vo.NewsK16server;
import com.dp.vo.NewsK16serverCity;
import com.dp.vo.NewsK16serverCityPK;
import com.dp.vo.NewsSnArea;
import com.dp.vo.TAreainfo;

@Service
@Transactional
public class k16servercityServiceImpl extends BaseImpl {
	/**
	 * Find all the findAllK16serverCityEntity Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsK16serverCity>
	 */
	public List<NewsK16serverCity> findAllK16serverCityEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsK16serverCity k16servercity=(NewsK16serverCity) hashMap.get("k16servercity");
		String areaids=(String) hashMap.get("areaids");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsK16serverCity n WHERE 1=1 ");
		if(k16servercity!=null){
			if(k16servercity.getK16serverCityPK().getSoftagent()!=null&& k16servercity.getK16serverCityPK().getSoftagent().toString().trim().length() > 0){
				sqlS.append(" AND n.k16serverCityPK.softagent like '%"
						+ k16servercity.getK16serverCityPK().getSoftagent() + "%'");
			}
			if(k16servercity.getK16server().getId()!=null&& k16servercity.getK16server().getId().toString().trim().length() > 0){
				sqlS.append(" AND n.k16server.id="
						+ k16servercity.getK16server().getId().trim() + "");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.k16serverCityPK.city.areaid in ("
					+ areaids + ")");
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<NewsK16serverCity> queryList=new ArrayList<NewsK16serverCity>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * 城市与服务器映射 记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllK16serverCityEntityCount(DataMap hashMap){
		Integer iCount = 0; 
		NewsK16serverCity k16servercity=(NewsK16serverCity) hashMap.get("k16servercity");
		String areaids=(String) hashMap.get("areaids");
		StringBuffer sqlS = new StringBuffer("SELECT count(n.k16serverCityPK.softagent) FROM NewsK16serverCity n WHERE 1=1 ");
		if(k16servercity!=null){
			if(k16servercity.getK16serverCityPK().getSoftagent()!=null&& k16servercity.getK16serverCityPK().getSoftagent().toString().trim().length() > 0){
				sqlS.append(" AND n.k16serverCityPK.softagent like '%"
						+ k16servercity.getK16serverCityPK().getSoftagent() + "%'");
			}
			if(k16servercity.getK16server().getId()!=null&& k16servercity.getK16server().getId().toString().trim().length() > 0){
				sqlS.append(" AND n.k16server.id="
						+ k16servercity.getK16server().getId().trim() + "");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.k16serverCityPK.city.areaid in ("
					+ areaids + ")");
		}
		
		Query query=null;
		try {
			query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * SN与区域对应信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllSnAreaEntityCount(DataMap hashMap){
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.newsSnAreaPK.sn) FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		try {
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * Find all the NewsSnArea Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<SnAreaVo>
	 */
	public List<SnAreaVo> findAllSnAreaEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		String areaids=(String) hashMap.get("areaids");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<SnAreaVo> list=new ArrayList<SnAreaVo>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			List<NewsSnArea> queryList=new ArrayList<NewsSnArea>();
			queryList = query.getResultList();
			SnAreaVo snAreaVo = null;
			for(NewsSnArea snarea_temp:queryList){
				snAreaVo=new SnAreaVo();
				snAreaVo.setSn(snarea_temp.getNewsSnAreaPK().getSn());
				snAreaVo.setAreaid(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaid()+"");
				snAreaVo.setAreaname(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaname());
				list.add(snAreaVo);
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * 服务器列表
	 * @return
	 */
	public List<NewsK16server> findAllK16serverEntity(){
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsK16server n WHERE 1=1 ");
		List<NewsK16server> queryList=new ArrayList<NewsK16server>();
		try {
			Query query = em.createQuery(sqlS.toString());
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	/**
	 * 增加
	 * @param dataMap
	 * @return resultb==null:操作失败；resultb为已存在记录中cityid
	 */
	public String addK16ServerCityE(DataMap hashMap) {
		NewsK16serverCity k16servercity=(NewsK16serverCity) hashMap.get("k16servercity");
		String areaids=(String) hashMap.get("areaids");
		String[] areaids_arry=areaids.split(",");
		String resultb="";
		try {
			
			for(String areaid:areaids_arry){
				this.sql="select count(*) from news_k16servercity where cityid="+areaid
				+" and softagent='"+k16servercity.getK16serverCityPK().getSoftagent()+"'";
				
				if(this.getOneInt()>0){
					resultb+=areaid+",";
				}else{
					this.sql="insert into news_k16servercity (cityid,softagent,serverid) values("+areaid
					+",'"+k16servercity.getK16serverCityPK().getSoftagent()+"',"+k16servercity.getK16server().getId()+")";
					this.em.createNativeQuery(sql).executeUpdate();
				}
			}
		
		} catch (Exception e) {
			logger.error("addK16ServerCityE fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=null;
		}
		if(!"".equals(resultb)){
			resultb=resultb.substring(0,resultb.length()-1);
		}
		return resultb;
	}
	
	public Boolean updateK16servercityE(DataMap paramMap) {
		NewsK16serverCity k16servercity_before=(NewsK16serverCity) paramMap.get("k16servercity_before");
		NewsK16serverCity k16servercity=(NewsK16serverCity) paramMap.get("k16servercity");
		Boolean resultb=true;
		try {
			
			if(!(k16servercity.getK16serverCityPK().getCity().getAreaid().equals(k16servercity_before.getK16serverCityPK().getCity().getAreaid())&&k16servercity.getK16serverCityPK().getSoftagent().equals(k16servercity_before.getK16serverCityPK().getSoftagent()))){
				sql="select count(*) from news_k16servercity where cityid="
					+k16servercity.getK16serverCityPK().getCity().getAreaid()+" and softagent='"+k16servercity.getK16serverCityPK().getSoftagent()+"'";
				
				if(this.getOneInt()==0){
					return null;
				}
			}
			this.sql="update news_k16servercity set cityid=?,softagent=?,serverid=? where cityid=? and softagent=?";
			Query	query=em.createNativeQuery(sql);
		
			query.setParameter(1, k16servercity.getK16serverCityPK().getCity().getAreaid());
			query.setParameter(2, k16servercity.getK16serverCityPK().getSoftagent());
			query.setParameter(3, k16servercity.getK16server().getId());
			query.setParameter(4, k16servercity_before.getK16serverCityPK().getCity().getAreaid());
			query.setParameter(5, k16servercity_before.getK16serverCityPK().getSoftagent());
			query.executeUpdate();
			
		} catch (Exception e) {
			logger.error("updateK16servercityE fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	
	//删除
	public boolean deleteK16serversnEByIds(String ids) {
		Boolean resultb=true;
		try {
		
			String[] idarys=ids.split(",");
			this.sql="delete from news_k16serversn where sn=?";
			Query	query=em.createNativeQuery(sql);
		
			for(String sn:idarys){
				query.setParameter(1, sn);
				query.executeUpdate();
			}
			
		} catch (Exception e) {
			logger.error("deleteK16serversnEByIds fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	
	/**
	 * 预处理得到检索条件中所有的areaid
	 * @param area_id
	 * @return
	 */
	public String findAllAreaids(Integer area_id) {
		String areaids=null;
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	List<Integer> areaid_templist=new ArrayList<Integer>();
	        	areaid_templist = getAreaid_templist(queryList,area_id,areaid_templist);
	        	areaids="";
	        	for(Integer areaid_temp:areaid_templist){
	        		areaids+="'"+areaid_temp+"',";
	        	}
	        	if(!"".equals(areaids)){
	        		areaids=areaids.substring(0, areaids.length()-1);
	        	}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areaids;
	}
	
	private List<Integer> getAreaid_templist(List<TAreainfo> tailist,int areaid_temp,List<Integer> areaid_templist){
		areaid_templist.add(areaid_temp);
		for(TAreainfo tc3:tailist){
			if(tc3.getUpareaid().equals(areaid_temp)){
				if(tc3.getAreaid()!=0){
					getAreaid_templist(tailist,tc3.getAreaid(),areaid_templist);
				}
			}
		}
		return areaid_templist;
	}
	
	public Object getSingleResult(Query query) {
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}

	public List<TAreainfo> findAllAreainfo(int upareaid) {
		List<TAreainfo> queryList=new ArrayList<TAreainfo>();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p WHERE p.upareaid=:upareaid ");
	    	sqlS.append(" order by p.areaid");
	        Query query = em.createQuery(sqlS.toString());      
	        query.setParameter("upareaid", upareaid);
	        queryList=query.getResultList();  
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return queryList;
	}
	
	public TAreainfo findAreainfoByid(Integer areaid) {
		TAreainfo o = null;
		try {
			o = this.em.find(TAreainfo.class, areaid);
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return o;
	}

	/**
	 * 获得areaid对应区域信息
	 * 1.编辑时使用
	 * @param areaid
	 * @return
	 */
	public SnAreaVo findAreaInfoByAreaid(String areaid) {
		SnAreaVo areainfo=new SnAreaVo();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	for(TAreainfo tai:queryList){
	        		if(tai.getAreaid().equals(Integer.parseInt(areaid))){
	        			List<TAreainfo> areainfo_templist=new ArrayList<TAreainfo>();
	    	        	areainfo_templist = getAreainfo_templist(queryList,tai,areainfo_templist);
	    	        	for(TAreainfo areainfo_temp :areainfo_templist){
	    	        		if(areainfo_temp.getArealevel()==3){
	    	        			areainfo.setArea_county(areainfo_temp.getAreaid()+"");
	    	        		}else if(areainfo_temp.getArealevel()==2){
	    	        			areainfo.setArea_city(areainfo_temp.getAreaid()+"");
	    	        		}else if(areainfo_temp.getArealevel()==1){
	    	        			areainfo.setArea_province(areainfo_temp.getAreaid()+"");
	    	        		}else if(areainfo_temp.getArealevel()==0){
	    	        			areainfo.setArea_province(areainfo_temp.getAreaid()+"");
	    	        		}
	    	        	}
	    	        	break;
	        		}
	        	}
	        	if(areainfo.getArea_county()!=null){
	        		areainfo.setAreaid(areainfo.getArea_county());
        		}else if(areainfo.getArea_city()!=null){
	        		areainfo.setAreaid(areainfo.getArea_city());
        		}else if(areainfo.getArea_province()!=null){
	        		areainfo.setAreaid(areainfo.getArea_province());
        		}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areainfo;
	}
	/**
	 * 根据区域id递归得到上级对象
	 * 1.编辑时使用
	 * @param tailist
	 * @param tai_temp
	 * @param areainfo_templist
	 * @return
	 */
	private List<TAreainfo> getAreainfo_templist(List<TAreainfo> tailist,TAreainfo tai_temp,List<TAreainfo> areainfo_templist){
		areainfo_templist.add(tai_temp);
		for(TAreainfo tc3:tailist){
			if(tc3.getAreaid()!=0&&tc3.getAreaid().equals(tai_temp.getUpareaid())){
				getAreainfo_templist(tailist,tc3,areainfo_templist);
			}
		}
		return areainfo_templist;
	}

	public NewsK16serverCity findK16servercityByid(NewsK16serverCityPK k16serverCityPK) {
		NewsK16serverCity o = null;
		try {
			o = this.em.find(NewsK16serverCity.class, k16serverCityPK);
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return o;
	}

	//删除
	public boolean deleteK16serverCity(List<NewsK16serverCity> scvList) {
		boolean resultb=true;
		try {
		
			this.sql="delete from news_k16servercity where cityid=? and softagent=?";
			Query	query=em.createNativeQuery(sql);
			for(NewsK16serverCity scv:scvList){
				query.setParameter(1, scv.getK16serverCityPK().getCity().getAreaid());
				query.setParameter(2, scv.getK16serverCityPK().getSoftagent());
				query.executeUpdate();
			}
			
		} catch (Exception e) {
			logger.error("deleteK16serverCity fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	public List<String> findSoftagentL() {
		List<String> l=new ArrayList<String>();
		try {
				this.sql="select distinct magent from news_platforminfo";
				List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
				for(Object[]o:tempp)
				{
					l.add(this.getString(o[0]));
				}
		
		} catch (Exception e) {
			logger.error("findSoftagentL fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return l;
	}

}
